home *** CD-ROM | disk | FTP | other *** search
/ Programmer Plus 2007 / Programmer-Plus-2007.iso / Programming / SQL and Data Base / Logic Works ERwin 3.5 / data.1 / erwrelin.fiv < prev    next >
Encoding:
Text File  |  1998-01-28  |  6.5 KB  |  196 lines
  1. /* ------------------------------------------------------------------------
  2. --    ERWRELIN.FIV                                                          --
  3. --    Copyright (C) Logic Works, Inc. 1994                                 --
  4. --                                                                       --
  5. --  Stored function to insert ERwin relationships into the                  --
  6. --  Oracle CASE 5.1 dictionary                                             --
  7. ------------------------------------------------------------------------- */
  8. CREATE OR REPLACE FUNCTION ERWRELIN(pEntFrom    CI_ENTITIES.NAME%TYPE,
  9.                             pEntTo        CI_ENTITIES.NAME%TYPE,
  10.                             pEntFromID    CI_ENTITIES.ID%TYPE,
  11.                             pEntToID    CI_ENTITIES.ID%TYPE,
  12.                             pOptional    CI_RELATIONSHIP_ENDS.OPTIONAL%TYPE,
  13.                             pName1        CI_RELATIONSHIP_ENDS.NAME%TYPE,
  14.                             pName2        CI_RELATIONSHIP_ENDS.NAME%TYPE,
  15.                             pApp        CI_APPLICATION_SYSTEMS.NAME%TYPE,
  16.                             pVersion    CI_APPLICATION_SYSTEMS.VERSION%TYPE,
  17.                             pType        SMALLINT,
  18.                             pCardinality CI_RELATIONSHIP_ENDS.ID%TYPE
  19.                             )
  20.         RETURN CI_RELATIONSHIP_ENDS.ID%TYPE
  21.         AS
  22.              TYPE recEntity IS RECORD
  23.                 (APPLICATION_SYSTEM_OWNED_BY   CI_ENTITIES.APPLICATION_SYSTEM_OWNED_BY%TYPE,
  24.                  ID CI_ENTITIES.ID%TYPE,
  25.                  NAME CI_ENTITIES.NAME%TYPE);
  26.             TYPE recRelend IS RECORD
  27.                 (DEGREE    CI_RELATIONSHIP_ENDS.DEGREE%TYPE,
  28.                  FROM_ENTITY_REFERENCE    CI_RELATIONSHIP_ENDS.FROM_ENTITY_REFERENCE%TYPE,
  29.                  ID CI_RELATIONSHIP_ENDS.ID%TYPE,
  30.                  NAME CI_RELATIONSHIP_ENDS.NAME%TYPE,
  31.                  TO_ENTITY_REFERENCE   CI_RELATIONSHIP_ENDS.TO_ENTITY_REFERENCE%TYPE);
  32.              prRelend        recRelend;
  33.             stRelend        ciorelationship_end.data;
  34.             stEntity        cioentity.data;
  35.             prEntity        recEntity;
  36.             ToEntityRef        CI_ENTITIES.ID%TYPE;
  37.             FromEntityRef    CI_ENTITIES.ID%TYPE;
  38.             RelEnd1            CI_RELATIONSHIP_ENDS.ID%TYPE;
  39.             RelEnd2            CI_RELATIONSHIP_ENDS.ID%TYPE;
  40.             EndInsFail        EXCEPTION;
  41.               act_status        varchar2(100);
  42.             act_warnings    varchar2(100);
  43. BEGIN
  44.     if cdapi.initialized = false then
  45.         RETURN -1;
  46.     end if;
  47.              BEGIN
  48.                  IF pVersion = 0 THEN 
  49.                      SELECT APPLICATION_SYSTEM_OWNED_BY, ID, NAME INTO prEntity
  50.                          FROM CI_ENTITIES
  51.                          WHERE    CI_ENTITIES.NAME=UPPER(pEntFrom) 
  52.                          AND CI_ENTITIES.APPLICATION_SYSTEM_OWNED_BY = 
  53.                          (SELECT ID FROM CI_APPLICATION_SYSTEMS WHERE 
  54.                          CI_APPLICATION_SYSTEMS.NAME = UPPER(pApp) AND
  55.                          CI_APPLICATION_SYSTEMS.LATEST_VERSION_FLAG='Y');
  56.                  ELSE
  57.                      SELECT APPLICATION_SYSTEM_OWNED_BY, ID, NAME INTO prEntity
  58.                          FROM CI_ENTITIES
  59.                          WHERE    CI_ENTITIES.NAME=UPPER(pEntFrom) 
  60.                          AND CI_ENTITIES.APPLICATION_SYSTEM_OWNED_BY = 
  61.                          (SELECT ID FROM CI_APPLICATION_SYSTEMS WHERE 
  62.                          CI_APPLICATION_SYSTEMS.NAME = UPPER(pApp) AND
  63.                          CI_APPLICATION_SYSTEMS.VERSION=pVersion);
  64.                  END IF;
  65.                  dbms_output.put_line('In relationship: From entity found '||prEntity.ID);
  66.                  FromEntityRef := prEntity.ID;
  67.              EXCEPTION
  68.                 WHEN OTHERS THEN
  69.                     IF pEntFromID = 0 THEN
  70.                         RETURN  -1;
  71.                     ELSE
  72.                         FromEntityRef := pEntFromID;
  73.                     END IF;
  74.              END;
  75.             BEGIN
  76.                 IF pVersion = 0 THEN 
  77.                     SELECT APPLICATION_SYSTEM_OWNED_BY, ID, NAME INTO prEntity
  78.                         FROM CI_ENTITIES
  79.                         WHERE    CI_ENTITIES.NAME=UPPER(pEntTo) 
  80.                         AND CI_ENTITIES.APPLICATION_SYSTEM_OWNED_BY = 
  81.                         (SELECT ID FROM CI_APPLICATION_SYSTEMS WHERE 
  82.                         CI_APPLICATION_SYSTEMS.NAME = UPPER(pApp) AND
  83.                         CI_APPLICATION_SYSTEMS.LATEST_VERSION_FLAG='Y');
  84.                 ELSE
  85.                     SELECT APPLICATION_SYSTEM_OWNED_BY, ID, NAME INTO prEntity
  86.                         FROM CI_ENTITIES
  87.                         WHERE    CI_ENTITIES.NAME=UPPER(pEntTo) 
  88.                         AND CI_ENTITIES.APPLICATION_SYSTEM_OWNED_BY = 
  89.                         (SELECT ID FROM CI_APPLICATION_SYSTEMS WHERE 
  90.                         CI_APPLICATION_SYSTEMS.NAME = UPPER(pApp) AND
  91.                         CI_APPLICATION_SYSTEMS.VERSION=pVersion);
  92.                 END IF;
  93.                 dbms_output.put_line('In relationship: To entity found '||prEntity.ID);
  94.                 ToEntityRef := prEntity.ID;
  95.             EXCEPTION
  96.                 WHEN OTHERS THEN
  97.                     IF pEntToID = 0 THEN
  98.                         RETURN -1;
  99.                     ELSE
  100.                         ToEntityRef :=     pEntToID;
  101.                     END IF;
  102.             END;
  103.             IF pType = 9 THEN
  104.                 BEGIN
  105.                     stEntity.v.NAME := pEntTo;
  106.                     stEntity.i.NAME := true;
  107.                      stEntity.v.SUPERTYPE_REFERENCE := FromEntityRef;
  108.                     stEntity.i.SUPERTYPE_REFERENCE := true;
  109.                     cdapi.open_activity;
  110.                     cioentity.upd(ToEntityRef,stEntity);
  111.                      cdapi.validate_activity(act_status, act_warnings);
  112.                     cdapi.close_activity(act_status);
  113.                     if act_status != 'Y' then
  114.                          cdapi.abort_activity;
  115.                         RETURN -1;
  116.                     else
  117.                         RETURN ToEntityRef;
  118.                     end if;
  119.                 EXCEPTION
  120.                     WHEN OTHERS THEN
  121.                         if cdapi.stacksize > 0 then 
  122.                             if cdapi.activity is not null then 
  123.                                 cdapi.abort_activity;
  124.                             end if;
  125.                         else
  126.                             if cdapi.activity is not null then 
  127.                                 cdapi.abort_activity;
  128.                             end if;
  129.                         end if;
  130.                         RETURN  -1;
  131.                 END;
  132.             ELSE
  133.                 SELECT R1.DEGREE,R1.FROM_ENTITY_REFERENCE,R1.ID,
  134.                     R1.NAME, R1.TO_ENTITY_REFERENCE
  135.                     INTO prRelend
  136.                     FROM CI_RELATIONSHIP_ENDS R1, 
  137.                     CI_RELATIONSHIP_ENDS R2
  138.                     WHERE R1.FROM_ENTITY_REFERENCE=FromEntityRef
  139.                     AND R1.TO_ENTITY_REFERENCE=ToEntityRef
  140.                     AND R1.NAME=pName1 AND
  141.                     R1.OTHER_RELATIONSHIP_END_REF=R2.ID AND
  142.                     R2.FROM_ENTITY_REFERENCE=ToEntityRef AND
  143.                     R2.TO_ENTITY_REFERENCE=FromEntityRef;
  144.                 dbms_output.put_line('Relationship end found');    
  145.                 RETURN prRelend.ID;
  146.             END IF;                         
  147.         EXCEPTION
  148.             WHEN NO_DATA_FOUND THEN
  149.                BEGIN
  150.                    cdapi.open_activity;    
  151.                    RelEnd1 := ERWRENIN(FromEntityRef,pName1,'ONE OR MORE',
  152.                                     pOptional,-1);
  153.                    IF RelEnd1 = -1    THEN
  154.                         raise EndInsFail;
  155.                    END IF;
  156.                    IF pType = 2 OR pType = 7 THEN
  157.                        RelEnd2 := ERWRENIN(ToEntityRef,pName2,'ONE AND ONLY ONE',
  158.                                     pOptional,RelEnd1);
  159.                    ELSE
  160.                        RelEnd2 := ERWRENIN(ToEntityRef,pName2,'ONE OR MORE',
  161.                                     pOptional,RelEnd1);
  162.                    END IF;    
  163.                    IF RelEnd2 = -1 THEN
  164.                            raise EndInsFail;
  165.                    END IF;
  166.                    cdapi.validate_activity(act_status, act_warnings);
  167.                    cdapi.close_activity(act_status);
  168.                    if act_status != 'Y' then
  169.                         cdapi.abort_activity;
  170.                         RETURN -1;
  171.                    else
  172.                         RETURN RelEnd2;
  173.                    end if;
  174.                EXCEPTION
  175.                       WHEN EndInsFail THEN
  176.                         if cdapi.activity is not null then 
  177.                             cdapi.abort_activity;
  178.                         end if;
  179.                               RETURN -1;
  180.                    WHEN OTHERS THEN
  181.                         if cdapi.stacksize > 0 then 
  182.                             if cdapi.activity is not null then 
  183.                                 cdapi.abort_activity;
  184.                             end if;
  185.                         else
  186.                             if cdapi.activity is not null then 
  187.                                 cdapi.abort_activity;
  188.                             end if;
  189.                         end if;
  190.                         RETURN  -1;
  191.                END;                                                                                                    
  192.             WHEN OTHERS THEN
  193.                 RETURN -1;
  194. END ERWRELIN;
  195. /
  196.